package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DaoSugar {
    @Select("SELECT sum(cargo_num*amount) value FROM order_info WHERE create_time>='2023-01-06' and create_time<'2023-01-07'\n")
    Double get1();

    @Select("SELECT d.name,sum( i.amount) value FROM order_info i\n" +
            "INNER JOIN order_cargo c ON  c.order_id=i.id\n" +
            "INNER JOIN base_dic d  ON d.id=c.cargo_type\n" +
            "GROUP BY d.name")
    List<Sugar> get2();

    @Select("SELECT o.org_name name,\n" +
            "sum(t.actual_distance) actual,\n" +
            "count(t.start_org_name) num,\n" +
            "sum(TIMESTAMPDIFF(SECOND,t.actual_start_time,t.actual_end_time)) time,\n" +
            "sum(TIMESTAMPDIFF(SECOND,t.actual_start_time,t.actual_end_time))/count(t.start_org_name) AvgTime,\n" +
            "sum(t.actual_distance)/count(t.start_org_name) AvgActual\n" +
            "FROM transport_task t\n" +
            "INNER JOIN line_base_info i ON t.line_id=i.id \n" +
            "INNER JOIN base_organ o ON i.org_id=o.id\n" +
            "GROUP BY o.org_name")
    List<Sugar1> get3();

    @Select("SELECT r1.name,count(r.id) value FROM transport_task_detail d\n" +
            "INNER JOIN transport_task t ON d.transport_task_id=t.id \n" +
            "INNER JOIN line_base_info i ON t.line_id=i.id \n" +
            "INNER JOIN base_organ o ON i.org_id=o.id \n" +
            "INNER JOIN base_region_info r ON o.region_id=r.id\n" +
            "INNER JOIN base_region_info r1 ON r.parent_id=r1.id\n" +
            "GROUP BY r1.name")
    List<Sugar> get4();

    @Select("SELECT d.name,count(*) value FROM order_info i\n" +
            "INNER JOIN order_cargo c ON i.id=c.order_id \n" +
            "INNER JOIN base_dic d ON c.cargo_type=d.id\n" +
            "GROUP BY d.name")
    List<Sugar> get5();

    @Select("SELECT r.name,count(*) value FROM express_task_delivery e\n" +
            "INNER JOIN base_organ o ON e.org_id = o.id and o.org_level=1\n" +
            "INNER JOIN base_region_info r ON o.region_id = r.id\n" +
            "GROUP BY r.name\n" +
            "UNION\n" +
            "SELECT r1.name,count(*) value FROM express_task_delivery e\n" +
            "INNER JOIN base_organ o ON e.org_id = o.id and o.org_level=2\n" +
            "INNER JOIN base_region_info r ON o.region_id = r.id\n" +
            "INNER JOIN base_region_info r1 ON  r.parent_id=r1.id\n" +
            "GROUP BY r1.name")
    List<Sugar> get6();

    @Select("SELECT if(d.name='已提交','完成运单数','发单数') name,count(*) value FROM transport_task t\n" +
            "INNER JOIN base_dic d ON t.`status`=d.id\n" +
            "GROUP BY d.NAME\n" +
            "UNION\n" +
            "SELECT '接单数' name,count(*) value FROM transport_task  ")
    List<Sugar> get7();

    @Select("SELECT '当日完成运输次数' name,count(actual_distance) value FROM transport_task WHERE create_time>='2023-01-06' and create_time<'2023-01-07'\n" +
            "UNION\n" +
            "SELECT '当日完成运输里程' name,sum(actual_distance) value FROM transport_task WHERE create_time>='2023-01-06' and create_time<'2023-01-07'\n" +
            "UNION\n" +
            "SELECT '当日完成运输时长' name,sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) value FROM transport_task WHERE create_time>='2023-01-06' and create_time<'2023-01-07' ")
    List<Sugar> get8();

    @Select("SELECT count(*) value FROM transport_task WHERE create_time>='2023-01-06' and create_time<'2023-01-07' \n")
    Double get9();
}
